package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import schedule.Appointment;
import schedule.AppointmentList;

public class JdbcSchedule {
	private Connection connection;
	
	public JdbcSchedule(){
		
	}
	
	private Connection getConnection() throws Exception{
		try{
			if(connection == null || connection.isClosed()){
				String url = "jdbc:mysql://localhost/terminplaner";
				String user = "root";
				String pwd = "";
				String driver = "com.mysql.jdbc.Driver";
				
				Class.forName(driver);
				connection = DriverManager.getConnection(url, user, pwd);
			}
		} catch(SQLException e){
			throw new SQLException("error while connection to database", e);
		} catch(ClassNotFoundException cnfe){
			throw new ClassNotFoundException("database driver not found", cnfe);
		}
		
		return connection;
	}
	
	public void save(Appointment app) throws Exception{
		if(app.getId() == 0)
			insert(app);
		else
			if(update(app) == 0)
				throw new Exception("no appointment updated");
	}
	
	private void insert(Appointment app) throws Exception{
		String sql = "INSERT INTO terminplaner (titel, start, ende, beschreibung) VALUES (?, ?, ?, ?)";
		
		try(PreparedStatement pstmt = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)){
			pstmt.setString(1, app.getTitle());
			pstmt.setTimestamp(2, new java.sql.Timestamp(app.getDate().getTime()));
			pstmt.setTimestamp(3, new java.sql.Timestamp(app.getEnd().getTime()));
			pstmt.setString(4, app.getDescription());
			
			pstmt.executeUpdate();
			ResultSet generated = pstmt.getGeneratedKeys();
			if(generated.next())
				app.setId(generated.getInt(1));
			else
				throw new Exception("error while reading auto-generated id");
			generated.close();
		} catch(SQLException e){
			throw new SQLException("error while saving the appointment: " +e.getMessage(), e);
		}
	}
	
	private int update(Appointment app) throws Exception{
		String sql = "UPDATE terminplaner SET titel = ?, start = ?, ende = ?, beschreibung = ? WHERE id = ?";
		try(PreparedStatement pstmt = getConnection().prepareStatement(sql)){
			pstmt.setString(1, app.getTitle());
			pstmt.setTimestamp(2, new java.sql.Timestamp(app.getDate().getTime()));
			pstmt.setTimestamp(3, new java.sql.Timestamp(app.getEnd().getTime()));
			pstmt.setString(4, app.getDescription());
			pstmt.setInt(5, app.getId());
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("error while updating an appointment: " + e.getMessage(), e);
		} catch (Exception e1) {
			throw new Exception("error while updating an appointment: " + e1.getMessage(), e1);
		}
	}
	
	/**
	 * 
	 * @return a List with all Appointments from the database
	 * @throws Exception
	 */
	public List<Appointment> selectAll() throws Exception{
		List<Appointment> list = new AppointmentList();
		String sql = "SELECT id, titel, start, ende, beschreibung FROM terminplaner";
		try(PreparedStatement pstmt = getConnection().prepareStatement(sql); ResultSet rs = pstmt.executeQuery()){
			while(rs.next()){
				Appointment app = new Appointment(rs.getString("titel"), rs.getTimestamp("start"), rs.getTimestamp("ende"), rs.getString("beschreibung"));
				app.setId(rs.getInt("id"));
				list.add(app);
			}
		} catch (SQLException e) {
			throw new SQLException("error while loading appointments: " + e.getMessage(), e);
		} catch (Exception e) {
			throw new Exception("error while loading appointments: " + e.getMessage(), e);
		}
		return list;
	}
	
	public int delete(Appointment app) throws Exception{
		String sql = "DELETE FROM terminplaner WHERE id = ?";
		try(PreparedStatement pstmt = getConnection().prepareStatement(sql)){
			pstmt.setInt(1, app.getId());
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException("error while trying to delete an appointment: " + e.getMessage(),e);
		} catch (Exception e1) {
			throw new Exception("error while trying to delete an appointment: " + e1.getMessage(), e1);
		}
	}
}
